<?php
/**
 * Once-off database creating script for the Eventsmanager software.
 *
 * Both the system module classes and user module classes inherits this class.
 * @package Eventsmanager
 * @author  Thato Selebogo
 */

//connect to MySQL
$db = mysqli_connect('localhost','root','softdev3','eventsmanager_v2') or
                die ('cannot connect');
//create the BUSER table
$query = "CREATE TABLE IF NOT EXISTS buser (
          buser_id          INTEGER UNSIGNED    NOT NULL AUTO_INCREMENT,
          buser_fname       VARCHAR(20)         NOT NULL,
          buser_lname       VARCHAR(20)         NOT NULL,
          buser_username    VARCHAR(20)         NOT NULL,
          buser_pword       CHAR(41)            NOT NULL,
          buser_email       VARCHAR(100)        NOT NULL,

          UNIQUE (buser_email),
          PRIMARY KEY (buser_id)
          )";

$db->query($query) or die(mysqli_error($db));

//create the EVENT table
$query = "CREATE TABLE IF NOT EXISTS event (
          event_id          INTEGER UNSIGNED    NOT NULL AUTO_INCREMENT,
          buser_id          INTEGER UNSIGNED    NOT NULL,
          event_name        VARCHAR(20)         NOT NULL,
          event_address     VARCHAR(50)         NOT NULL,
          event_logo        LONGBLOB,
          event_discr       TEXT                NOT NULL,
          event_type        VARCHAR(15)         NOT NULL,
          event_loc_lat     FLOAT(9,6)          NOT NULL,
          event_loc_long    FLOAT(9,6)          NOT NULL,

          PRIMARY KEY (event_id),
          FOREIGN KEY (buser_id) REFERENCES buser (buser_id)
          )";

$db->query($query) or die(mysqli_error($db));

//create the CUSER table
$query = "CREATE TABLE IF NOT EXISTS cuser (
          cuser_id          INTEGER UNSIGNED    NOT NULL AUTO_INCREMENT,
          cuser_fname       VARCHAR(20)         NOT NULL,
          cuser_lname       VARCHAR(20)         NOT NULL,
          cuser_username    VARCHAR(20)         NOT NULL,
          cuser_pword       CHAR(41)            NOT NULL,
          cuser_email       VARCHAR(100),

          UNIQUE (cuser_email),
          PRIMARY KEY (cuser_id)
          )";

$db->query($query) or die(mysqli_error($db));

//create the OPTIME table
$query = "CREATE TABLE IF NOT EXISTS optime (
          optime_id          INTEGER UNSIGNED    NOT NULL AUTO_INCREMENT,
          optime_open        TIME                NOT NULL,
          optime_close       TIME                NOT NULL,
          event_id           INTEGER UNSIGNED    NOT NULL,

          PRIMARY KEY (optime_id),
          FOREIGN KEY (event_id) REFERENCES event (event_id)
          )";

$db->query($query) or die(mysqli_error($db));

//create the TIMETABLE table
$query = "CREATE TABLE IF NOT EXISTS timetable (
          timetable_id          INTEGER UNSIGNED    NOT NULL AUTO_INCREMENT,
          cuser_id              INTEGER UNSIGNED    NOT NULL,

          PRIMARY KEY (timetable_id),
          FOREIGN KEY (cuser_id) REFERENCES cuser (cuser_id)
          )";

$db->query($query) or die(mysqli_error($db));

//create the GENERATE table. This table is used to break the Many-to-many relationship
//between TIMETABLE and EVENT.
$query = "CREATE TABLE IF NOT EXISTS generate (
          generate_id          INTEGER UNSIGNED    NOT NULL AUTO_INCREMENT,
          event_id             INTEGER UNSIGNED    NOT NULL,

          PRIMARY KEY (generate_id),
          FOREIGN KEY (event_id) REFERENCES event (event_id)
          )";

$db->query($query) or die(mysqli_error($db));
?>
